﻿CREATE TRIGGER tr_ContractCondition_PTIByGrossIncome
ON dbo.t_ContractCondition_PTIByGrossIncome
FOR INSERT, UPDATE
AS

IF UPDATE(ContractConditionCode) AND
	 EXISTS(SELECT * FROM inserted WHERE ContractConditionCode <= '  ')BEGIN
	ROLLBACK TRAN
	RAISERROR('Contract Condition Code must be specified.',16,1)
	RETURN
END

DECLARE @Res int
DECLARE @T TABLE(ID int IDENTITY, IncomeFrom int,IncomeTo int,PTI int)
INSERT INTO @T
SELECT PTI.GrossIncomeFrom,PTI.GrossIncomeTo,PTI.PTI FROM t_ContractCondition_PTIByGrossIncome PTI
INNER JOIN inserted i ON i.ContractConditionCode = PTI.ContractConditionCode
ORDER BY GrossIncomeFrom;

WITH Incomes AS(
SELECT TOP 1 ID,IncomeTo Income,PTI FROM @T
UNION ALL
SELECT PTI.ID,IncomeTo, PTI.PTI
FROM @T PTI INNER JOIN Incomes ON PTI.ID = Incomes.ID+1 AND PTI.IncomeFrom = Incomes.Income+1 AND PTI.PTI > Incomes.PTI
)
SELECT @Res = COUNT(*) FROM Incomes

IF @Res = (SELECT COUNT(*) FROM @T) RETURN

ROLLBACK TRAN
RAISERROR('Wrong sequence.',16,1)
